package group.cyfc.studygo.dao;

import com.sun.org.apache.xpath.internal.operations.Mod;
import group.cyfc.studygo.entity.Gateway;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;

@Repository
public interface GatewayRepository extends JpaRepository<Gateway,Long> {

    /**
     * 查找对应id的关卡
     * @param id
     * @param is
     * @return
     */
    Gateway findGatewayByIdAndIsDel(Long id,Boolean is);

    /**
     * 获取对应类型所有关卡
     * @param grade
     * @param subject
     * @param is
     * @return
     */
    List<Gateway> findAllByGradeAndSubjectAndIsDel(String grade,String subject,Boolean is);

    /**
     * 获取用户可选关卡
     * @param grade
     * @param subject
     * @param number
     * @param sort
     * @return
     */
    @Query("select e from Gateway e where e.grade = :grade and e.subject = :subject and e.number <= :number and e.isDel = 0")
    List<Gateway> getUserGateways(@Param("grade") String grade, @Param("subject") String subject,
                                  @Param("number") Integer number,Sort sort);

    /**
     * 获得对应类型所有关卡所绑定的试卷号
     * @param grade
     * @param subject
     * @return
     */
    @Query(nativeQuery = true,value = "select paper_id from gateway where grade = :grade and subject = :subject and is_del = 0 order by number ASC ")
    List<String> getPaperIdByGradeAndSubject(@Param("grade")String grade,@Param("subject")String subject);

    @Modifying
    @Transactional(rollbackOn = Exception.class)
    @Query(nativeQuery = true,value = "update gateway set paper_id = :paperId where id = :id and is_del = 0")
    void changeGateway(@Param("id")Long id,@Param("paperId")Long paperId);

    /**
     * 获得grade年级subject类型的关卡数目
     * @param grade
     * @param subject
     * @return
     */
    @Query(nativeQuery = true,value = "select count(id) from gateway where grade = :grade and subject = :subject and is_del = 0")
    Integer getNumber(@Param("grade")String grade,@Param("subject")String subject);

    /**
     * 更新关卡号
     * @param grade
     * @param subject
     * @param number
     */
    @Modifying
    @Transactional(rollbackOn = Exception.class)
    @Query("update Gateway g set g.number = g.number-1 where g.grade = :grade and g.subject = :subject and g.number>:number and g.isDel = 0")
    void updateGatewayNumbers(@Param("grade")String grade,@Param("subject") String subject,@Param("number")Integer number);

    /**
     * 查找对应标签的关卡
     * @param grade
     * @param subject
     * @param number
     * @return
     */
    Gateway findGatewayByGradeAndSubjectAndNumberAndIsDel(String grade,String subject,Integer number,Boolean is);

    /**
     * 将is_del标志置为1表示数据删除
     * @param id
     */
    @Modifying
    @Transactional(rollbackOn = Exception.class)
    @Query(nativeQuery = true,value = "update gateway set is_del = 1 where id = :id")
    void deleteGateway(Long id);

    /**
     * 通过id查找关卡
     * @param id
     * @param is
     * @return
     */
    Gateway findByIdAndIsDel(Long id,Boolean is);
}
